package edu.muohio.cse385.drawsomething;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class DatabaseConnection {
	private static boolean			_hasDriverLoaded;
    private static Connection		_connection;
    private static Properties		_properties;
	private static Statement		_statement;

	
	/**
	 * Loads connection properties from file.
	 * 
	 * @param propertiesPath
	 *		path to a key-value properties file
	 * @return 
	 *		true if successful; false otherwise
	 */
	public static boolean loadProperties(String propertiesPath) {
		if (propertiesPath == null || propertiesPath.isEmpty()) {
			return false;
		}
		
		try {
			_properties = new Properties();
			_properties.load(new FileReader(propertiesPath));
		}
		catch (FileNotFoundException e) {
			System.err.println(
				"ERR DatabaseConnection.loadProperties: property file not valid: " +
				e.getLocalizedMessage()
			);
			
			_properties = null;
			return false;
		}
		catch (IOException e) {
			System.err.println(
				"ERR DatabaseConnection.loadProperties: failed to read property file: " +
				e.getLocalizedMessage()
			);
			
			_properties = null;
			return false;
		}
		
		return true;
	}
	
	
	/**
	 * Gives a connection to the database. Creates a new connection if one is not 
	 * currently active.
	 * 
	 * @return 
	 *		connection if successful; null otherwise
	 */
    public static Connection getConnection() {
		// No-op without properties
		if (_properties == null) {
			return null;
		}
		
		// One-time load driver
		if (!_hasDriverLoaded) {
			_hasDriverLoaded = loadDriver();
			
			if (!_hasDriverLoaded) {
				// Nothing can be done if driver can't be loaded: abort
				System.exit(1);
			}
		}
		
		if (_connection == null) {
			try {
				// smithre5: localhost:3307 is project-specific
				_connection = DriverManager.getConnection(
					String.format(
						"jdbc:mysql://%s:%s/%s?user=%s&password=%s",
						_properties.getProperty("host", "localhost"),
						_properties.getProperty("port", "3307"),
						_properties.getProperty("database"),
						_properties.getProperty("user"),
						_properties.getProperty("password")
					),
					_properties
				);
			}
			catch (SQLException e) {
				_connection = null;
				
				System.err.println(
					"ERR DatabaseConnection.getConnection: failed to get connection: " +
					e.getLocalizedMessage()
				);
			}
		}
		
		return _connection;
	}

	
	/**
	 * Closes the currently active connection. Does nothing if there is no 
	 * active connection. Any uncommitted transactions are released and not
	 * applied to the database.
	 */
	public static void endConnection() {
		if (_connection == null) {
			return;
		}
		
		try {
			if (_statement != null) {
				_statement.clearBatch();
				_statement.close();
			}
			
			_connection.close();
		}
		catch (SQLException e) {
			System.err.println(
				"ERR DatabaseConnection.endConnection: failed to close connection: " +
				e.getLocalizedMessage()
			);
		}
		finally {
			_statement = null;
			_connection = null;
		}
	}
	
	
	/**
	 * Attempts to load MySQL JDBC driver.
	 * 
	 * @return 
	 *		true if successful; false otherwise
	 */
	private static boolean loadDriver() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			return true;
		}
		catch (Exception e) {
			System.err.println(
				"ERR DatabaseConnection.loadDriver: failed to load driver: " +
				e.getLocalizedMessage()
			);
			return false;
		}
	}
	
	
	/**
	 * Performs a query on the database. A connection has to be opened prior to
	 * using this.
	 * 
	 * @param command 
	 *		MySQL query command to execute
	 * @return 
	 *		ResultSet if successful; null otherwise
	 */
	public static ResultSet executeQuery(String command) {
		if (command == null || command.isEmpty() || _connection == null) {
			return null;
		}
		
		ResultSet result = null;
		
		try {
			if (_statement == null) {
				_statement = _connection.createStatement();
			}
			
			result = _statement.executeQuery(command);
		}
		catch (SQLException e) {
			System.err.println(
				"ERR DatabaseConnection.executeQuery: " +
				e.getLocalizedMessage()
			);
			
			result = null;
		}
		
		return result;
	}
	
	
	/**
	 * Performs a single, auto-commit query on the database. A connection is
	 * automatically opened for this.
	 * 
	 * @param command 
	 *		MySQL query command to execute
	 * @return 
	 *		ResultSet if successful; null otherwise
	 */
	public static ResultSet executeSingleQuery(String command) {
		if (command == null || command.isEmpty()) {
			return null;
		}
		
		if (getConnection() == null) {
			return null;
		}
		
		return executeQuery(command);
	}
	
	
	/**
	 * Performs an update on the database. A connection has to be opened prior to
	 * using this.
	 * 
	 * @param command 
	 *		MySQL query command to execute
	 * @return 
	 *		0 or higher if successful; -1 otherwise
	 */
	public static int executeUpdate(String command) {
		int result = -1;
		
		if (command == null || command.isEmpty() || _connection == null) {
			return result;
		}
		
		try {
			if (_statement == null) {
				_statement = _connection.createStatement();
			}
			
			result = _statement.executeUpdate(command);
		}
		catch (SQLException e) {
			System.err.println(
				"ERR DatabaseConnection.executeUpdate: " +
				e.getLocalizedMessage()
			);
			
			result = -1;
		}
		
		return result;
	}

	
	/**
	 * Performs a single, auto-commit update on the database. A connection is
	 * automatically opened for this.
	 * 
	 * @param command 
	 *		MySQL query command to execute
	 * @return 
	 *		0 or higher if successful; -1 otherwise
	 */
	public static int executeSingleUpdate(String command) {
		int result = -1;
		
		if (command == null || command.isEmpty()) {
			return result;
		}
		
		if (getConnection() == null) {
			return result;
		}
		
		return executeUpdate(command);
	}
}